# test01_mysql.py
import pymysql
# 创建mysql数据库的连接，创建游标对象
conn = pymysql.connect(
    host='192.168.88.128',
    port=3306,
    user='root',
    password='123456',
    db='tedu',
    charset='utf8'
)
cur = conn.cursor()
"""
# 执行sql命令，创建员工表emp，包含的字段ID、姓名、性别、生日、工资
create_table = '''
    create table emp(
        id int,
        name varchar(16),
        gender varchar(1),
        birthday date,
        salary decimal(7, 2)
    );
'''
cur.execute(create_table)

# 插入若干条数据
insert_sql = 'insert into emp values(%s,%s,%s,%s,%s)'
cur.executemany(insert_sql, [
    (1, '小明', '男', '2001-12-25', 8000),
    (2, '小红', '女', '2002-01-15', 5000),
    (3, '小军', '男', '2003-12-08', 6000)
])

# 查询出数据
select_sql = 'select * from emp'
cur.execute(select_sql)
print(cur.fetchall())

# 修改其中的数据
update_sql = 'update emp set salary=%s where id=%s'
cur.execute(update_sql, (10000, 1))
"""
# 删除部分数据
delete_sql = 'delete from emp where id=%s'
cur.execute(delete_sql, (2, ))

conn.commit()
# 关闭
cur.close()
conn.close()